﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Diagnostics;
using Excel = Microsoft.Office.Interop.Excel;

namespace GenDB
{
    public static class ClExcel
    {
        private static Hashtable hstExcelAppIDList = new Hashtable();

        public static void GetCurrExcelRunning()
        {
            hstExcelAppIDList.Clear();
            Process[] prg = Process.GetProcesses();
            int i = 0;
            for (i = 0; i < prg.Length; i++)
                if (prg[i].ProcessName.ToUpper() == "EXCEL")
                    hstExcelAppIDList.Add(prg[i].Id, true);
        }

        public static void CloseMyExcel()
        {
            int i = 0;
            int error = 1;

            while (error > 0)
            {
                error = 0;
                Process[] prg = Process.GetProcesses();

                for (i = prg.Length - 1; i >= 0; i--)
                {
                    if (hstExcelAppIDList.ContainsKey(prg[i].Id))
                    {
                        try
                        {
                            prg[i].Kill();
                        }
                        catch
                        {
                            error++;
                        }
                    }
                }
            }

            hstExcelAppIDList.Clear();
        }

        public static TableInfo GetTableInfo(string filename)
        {
            TableInfo tableInfo = null;

            int i = 0;

            GetCurrExcelRunning();

            Excel.Application excelObj = new Excel.Application();
            Excel.Workbook workbook = null;
            Excel.Sheets sheetList = null;
            Excel.Worksheet worksheet = null;

            workbook = excelObj.Workbooks.Open(filename, 0, true, 5, "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            sheetList = workbook.Worksheets;
            worksheet = (Excel.Worksheet)sheetList.get_Item(1);

            string value = "";

            //table name
            if (((Excel.Range)worksheet.Cells[1, 2]).Value2 != null)
            {
                value = ((Excel.Range)worksheet.Cells[1, 2]).Value2.ToString();
                if (string.IsNullOrEmpty(value) == false)
                    tableInfo = new TableInfo(value);
                else
                {
                    CloseMyExcel();
                    return tableInfo;
                }
            }


            int row = 3;
            int index = 1;
            while (((Excel.Range)worksheet.Cells[row, 1]).Value2 != null)
            {
                ColumnInfo col = null;
                index = 0;

                //column name
                value = ((Excel.Range)worksheet.Cells[row, ++index]).Value2.ToString();
                if (string.IsNullOrEmpty(value) == false)
                    col = new ColumnInfo(value);

                //data type
                if (col != null)
                {
                    if (((Excel.Range)worksheet.Cells[row, ++index]).Value2 != null)
                    {
                        value = ((Excel.Range)worksheet.Cells[row, index]).Value2.ToString();
                        if (string.IsNullOrEmpty(value) == false)
                            col.dataType = value;
                        else
                            col = null;
                    }
                }

                //data length
                if (col != null)
                {
                    if (((Excel.Range)worksheet.Cells[row, ++index]).Value2 != null)
                    {
                        value = ((Excel.Range)worksheet.Cells[row, index]).Value2.ToString();
                        if (string.IsNullOrEmpty(value) == false)
                            col.length = value;
                    }
                }

                //default value
                if (col != null)
                {
                    if (((Excel.Range)worksheet.Cells[row, ++index]).Value2 != null)
                    {
                        value = ((Excel.Range)worksheet.Cells[row, index]).Value2.ToString();
                        if (string.IsNullOrEmpty(value) == false)
                        {
                            if (value.ToUpper() == "AUTOINC")
                                col.isPrimaryKey = true;
                            else
                                col.defaultValue = value;
                        }
                    }
                }

                if (col != null)
                    tableInfo.columnInfos.Add(col);

                row++;
            }

            CloseMyExcel();
            return tableInfo;
        }
    }
}
